package course.dao.impl;

import course.dao.BaseDao;
import course.dao.SubjectDao;
import course.pojo.entity.Subject;
import course.pojo.vo.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author HLF
 * @date 2022/6/30 20:44
 */
public class SubjectDaoImpl extends BaseDao<Subject> implements SubjectDao {
    @Override
    public Subject getById(Integer suid, Connection connection) {
        String sql = "select * from t_subject where suid = ?;";
        return getOneBean(connection, sql, suid);
    }

    @Override
    public int addSubject(Subject subject, Connection connection) {
        String sql = "insert into t_subject values(?,?,?,?);";
        return insert(connection, sql, subject.getSuid(), subject.getSuname(), subject.getCid(), subject.getCname());
    }

    @Override
    public int updateById(Subject subject, Connection connection) {
        String sql = "update t_subject set suname = ?,cid=?,cname=? where suid = ?;";
        return update(connection, sql, subject.getSuname(), subject.getCid(), subject.getCname(), subject.getSuid());
    }

    @Override
    public int deleteById(Integer suid, Connection connection) {
        String sql = "delete from t_subject where suid = ?;";
        return update(connection, sql, suid);
    }

    @Override
    public List<Subject> getList(Connection connection) {
        String sql = "select * from t_subject;";
        return getBeanList(connection, sql);
    }

    /**/

    @Override
    public int deleteSelect(List<Integer> ids, Connection connection) {
        String sql = "delete from t_subject where suid in(?)";

        String sqlIN = ids.stream()
                .map(x -> String.valueOf(x))
                .collect(Collectors.joining(",", "(", ")"));

        sql = sql.replace("(?)", sqlIN);

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public long count(Connection connection) {
        String sql = "select count(*) from t_subject;";
        return (long) getCount(connection, sql);
    }

    /**
     * 分页
     * 新：pages(总页数)=[(total(总记录数)-1) / pageSize] +1
     *
     * @param pageNo 当前页码数
     * @return 返回通用的分页对象
     */
    @Override
    public Page<Subject> page(Integer pageNo, Connection connection) {
        Page<Subject> page = new Page<>();
        Integer pageSize = page.getPageSize();

        String sql = "select * from t_subject limit ?,?;";
        List<Subject> list = getBeanList(connection, sql, (pageNo - 1) * pageSize, pageSize);

        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setList(list);
        long total = this.count(connection);
        page.setTotal(total);

        int pages = (int) Math.ceil((double) total / pageSize);
        page.setPages(pages);

        return page;
    }

    /**
     * 只能查询当前student或teacher对应学院的所有课程，并进行分页
     */
    @Override
    public Page<Subject> page2(Integer pageNo, Integer cid, Connection connection) {
        Page<Subject> page = new Page<>();
        Integer pageSize = page.getPageSize();

        String sql = "select * from t_subject where cid = ? or cid = 0 limit ?,?;";
        List<Subject> list = getBeanList(connection, sql, cid, (pageNo - 1) * pageSize, pageSize);

        String sql2 = "select count(*) from t_subject where cid = ? or cid = 0;";
        long count = (long) getCount(connection, sql2, cid);

        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setList(list);
        page.setTotal(count);

        int pages = (int) Math.ceil((double) count / pageSize);
        page.setPages(pages);

        return page;
    }

    @Override
    public Page<Subject> ownPage(Integer pageNo, Integer tid, Connection connection) {
        Page<Subject> page = new Page<>();
        Integer pageSize = page.getPageSize();

        String sql = "select su.suid,su.suname,su.cname\n" +
                "FROM t_subject su INNER JOIN t_tea_sub ts\n" +
                "ON su.suid = ts.suid where tid = ? limit ?,?;";
        List<Subject> list = getBeanList(connection, sql, tid, (pageNo - 1) * pageSize, pageSize);

        String sql2 = "select count(*)\n" +
                "FROM t_subject su INNER JOIN t_tea_sub ts\n" +
                "ON su.suid = ts.suid where tid = ?;";
        long count = (long) getCount(connection, sql2, tid);

        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setList(list);
        page.setTotal(count);

        int pages = (int) Math.ceil((double) count / pageSize);
        page.setPages(pages);

        return page;
    }

    @Override
    public Page<Subject> ownPage2(Integer pageNo, Integer sid, Connection connection) {
        Page<Subject> page = new Page<>();
        Integer pageSize = page.getPageSize();

        String sql = "select su.suid,su.suname,su.cname\n" +
                "FROM t_subject su INNER JOIN t_stu_sub ts\n" +
                "ON su.suid = ts.suid where sid = ? limit ?,?;";
        List<Subject> list = getBeanList(connection, sql, sid, (pageNo - 1) * pageSize, pageSize);

        String sql2 = "select count(*)\n" +
                "FROM t_subject su INNER JOIN t_stu_sub ts\n" +
                "ON su.suid = ts.suid where sid = ?;";
        long count = (long) getCount(connection, sql2, sid);

        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setList(list);
        page.setTotal(count);

        int pages = (int) Math.ceil((double) count / pageSize);
        page.setPages(pages);

        return page;
    }

    /**
     * 按照学院关键字查找，并返回分页对象
     */
    @Override
    public Page<Subject> query(String suname, Integer pageNo, Connection connection) {
        Page<Subject> page = new Page<>();
        Integer pageSize = page.getPageSize();

        String sql = "select * from t_subject where suname like ? limit ?,?";
        String key = "%" + suname + "%";
        List<Subject> list = getBeanList(connection, sql, key, (pageNo - 1) * pageSize, pageSize);

        String sql2 = "select count(*) from t_subject where suname like ?;";
        long count = (long) getCount(connection, sql2, key);

        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setList(list);
        page.setTotal(count);

        int pages = (int) Math.ceil((double) count / pageSize);
        page.setPages(pages);

        return page;
    }

    @Override
    public Subject getBySuname(String suname, Connection connection) {
        String sql = "select * from t_subject where suname = ?;";
        return getOneBean(connection, sql, suname);
    }

    @Override
    public List<Subject> selectBathByIds(List<Integer> ids, Connection connection) {
        String sql = "select * from t_subject where suid in(?)";

        String sqlIN = ids.stream()
                .map(x -> String.valueOf(x))
                .collect(Collectors.joining(",", "(", ")"));

        sql = sql.replace("(?)", sqlIN);

        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet rs = preparedStatement.executeQuery();
            List<Subject> subjects = new ArrayList<>();
            while (rs.next()) {
                Subject subject = new Subject();
                subject.setSuid(rs.getInt(1));
                subject.setSuname(rs.getString(2));
                subject.setCid(rs.getInt(3));
                subject.setCname(rs.getString(4));
                subjects.add(subject);
            }
            return subjects;

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
